import os
import re
from flask import Flask, render_template, request, send_from_directory,url_for,redirect
import sqlite3
from pathlib import Path
from datetime import datetime



app = Flask(__name__)

app.config['IMAGE_BASE_DIR'] = r'D:\Code\LiuYao\Image\images'

# 数据库路径 - 请根据实际情况修改
DATABASE_PATH = r"D:\Code\LiuYao\craw-tieba-post\data\new_post-ly.db"

from werkzeug.exceptions import NotFound

def regexp(pattern, item):
    if item is None:
        return False
    return re.search(pattern, item) is not None
def is_valid_regex(pattern):
    try:
        re.compile(pattern)
        return True
    except re.error:
        return False
@app.route('/search', methods=['GET', 'POST'])
def search_post():
    if request.method == 'POST':
        search_query = request.form.get('post_id')

        # 判断是否是纯数字（按ID搜索）
        if search_query.isdigit():
            return redirect(url_for('show_post', post_id=int(search_query)))
        else:
            # 按标题搜索的逻辑
            conn = get_db_connection()
            posts = conn.execute('''
                SELECT post_id, title FROM data 
                WHERE title LIKE ? 
                LIMIT 20
            ''', (f'%{search_query}%',)).fetchall()
            conn.close()
            return render_template('search_results.html', posts=posts)

    return render_template('search.html')
@app.route('/image/<hash_value>')
def serve_image(hash_value):
    try:
        if len(hash_value) < 2:
            raise NotFound()

        sub_dir = os.path.join(hash_value[0], hash_value[1])
        image_dir = os.path.join(app.config['IMAGE_BASE_DIR'], sub_dir)
        return send_from_directory(image_dir, f"{hash_value}.jpg")

    except NotFound:
        # 返回默认图片
        return send_from_directory(app.static_folder, 'default.jpg')


# 注册模板过滤器
@app.template_filter('image_url')
def image_url_filter(hash_value):
    return url_for('serve_image', hash_value=hash_value) if hash_value else ""

# 自定义 datetime 过滤器
@app.template_filter('datetime')
def format_datetime(timestamp):
    if timestamp is None:
        return ""
    try:
        # 假设时间戳是秒级（Unix时间戳）
        dt = datetime.fromtimestamp(int(timestamp))
        return dt.strftime('%Y-%m-%d %H:%M:%S')
    except (ValueError, TypeError):
        return str(timestamp)

def get_db_connection():
    conn = sqlite3.connect(DATABASE_PATH)
    conn.row_factory = sqlite3.Row
    conn.create_function('REGEXP', 2, regexp)  # 注册自定义 REGEXP 函数

    return conn


@app.route('/')
def index():
    page = request.args.get('page', 1, type=int)
    per_page = 50

    # 获取搜索参数
    search_query = request.args.get('search', '').strip()
    search_field = request.args.get('field', 'title_summary')  # 默认字段为 title+summary

    conn = get_db_connection()

    query_params = []
    conditions = []

    if search_query:
        patterns = [p.strip() for p in search_query.split(';') if p.strip()]
        if not all(is_valid_regex(p) for p in patterns):
            return f"""你输入的字符串：{p},{type(p)} \n{'--'.join(patterns)} 不是有效的正则表达式！"""

        if patterns:
            if search_field == 'title_summary':
                for pattern in patterns:
                    conditions.append(f"(d.title REGEXP '{pattern}' OR d.summary REGEXP '{pattern}')")
            elif search_field == 'create_time':
                for pattern in patterns:
                    conditions.append(f"d.create_time REGEXP '{pattern}'")
            elif search_field == 'predict':
                for pattern in patterns:
                    conditions.append(f"d.predict REGEXP '{pattern}'")

    where_clause = ""
    if conditions:
        where_clause = "WHERE " + " OR ".join(conditions)

    # 获取满足条件的总数
    total_posts_query = f"SELECT COUNT(*) FROM data d {where_clause}"
    total_posts = conn.execute(total_posts_query, (*query_params,)).fetchone()[0]

    # 获取帖子列表，按回复数降序排列
    posts_query = f'''
        SELECT d.post_id, d.title, d.author_name, d.create_time, 
               d.reply_num, d.summary,
               COUNT(c.cid) AS actual_comments
        FROM data d
        LEFT JOIN comment c ON d.post_id = c.tid
        {where_clause}
        GROUP BY d.post_id
        ORDER BY d.reply_num DESC
        LIMIT ? OFFSET ?
    '''
    posts = conn.execute(posts_query, (*query_params, per_page, (page - 1) * per_page)).fetchall()

    # total_posts = conn.execute('SELECT COUNT(*) FROM data').fetchone()[0]
    total_pages = (total_posts + per_page - 1) // per_page

    conn.close()

    return render_template('index.html', posts=posts, page=page, total_pages=total_pages, total_posts=total_posts)

@app.route('/post/<int:post_id>')
def show_post(post_id):
    conn = get_db_connection()

    # 获取帖子基本信息
    post = conn.execute('''
        SELECT d.post_id, d.title, d.author_name, d.create_time, 
               d.reply_num, d.summary, d.img_urls
        FROM data d
        WHERE d.post_id = ?
    ''', (post_id,)).fetchone()

    if not post:
        return render_template('404.html', message=f"未找到ID为 {post_id} 的帖子"), 404

    # 获取主楼内容（floor=1的评论）
    main_content = conn.execute('''
        SELECT c.cid, c.content, c.user_id, c.timestamp, c.img_hashes, c.floor, c.post_id
        FROM comment c
        WHERE c.tid = ? AND c.floor = 1
        ORDER BY c.timestamp ASC
        LIMIT 1
    ''', (post_id,)).fetchone()

    # 获取所有主楼层评论（floor不为NULL的）
    main_floors = conn.execute('''
        SELECT c.cid, c.content, c.user_id, c.floor, c.timestamp, c.img_hashes, c.post_id
        FROM comment c
        WHERE c.tid = ? AND c.floor IS NOT NULL
        ORDER BY c.floor ASC, c.timestamp ASC
    ''', (post_id,)).fetchall()

    # 获取所有楼中楼回复（floor为NULL的）
    sub_replies = conn.execute('''
        SELECT c.cid, c.content, c.user_id, c.timestamp, c.img_hashes, c.post_id
        FROM comment c
        WHERE c.tid = ? AND c.floor IS NULL
        ORDER BY c.post_id ASC, c.timestamp ASC
    ''', (post_id,)).fetchall()

    # 将楼中楼回复按post_id分组
    replies_by_post = {}
    for reply in sub_replies:
        if reply['post_id'] not in replies_by_post:
            replies_by_post[reply['post_id']] = []
        replies_by_post[reply['post_id']].append(reply)

    # 获取关联的卦象数据
    gua_data = conn.execute('''
        SELECT g.gua, g.biangua, g.year, g.month, g.day, g.hour, g.minute,
               g.year_g, g.year_z, g.month_g, g.month_z, g.day_g, g.day_z,
               g.hour_g, g.hour_z, g.create_type
        FROM gua_data g
        WHERE g.tid = ?
    ''', (post_id,)).fetchall()

    conn.close()

    return render_template('post.html',
                           post=post,
                           main_content=main_content,
                           main_floors=main_floors,
                           replies_by_post=replies_by_post,
                           gua_data=gua_data)



@app.route('/favicon.ico')
def favicon():
    return send_from_directory('static', 'favicon.ico', mimetype='image/vnd.microsoft.icon')
if __name__ == '__main__':
    app.run(debug=True)